package com.aizhixin.lab.course.classes.service;

import com.aizhixin.lab.course.classes.domain.*;
import com.aizhixin.lab.course.classes.entity.*;
import com.aizhixin.lab.course.classes.repository.ReportFileRepository;
import lombok.extern.slf4j.Slf4j;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.core.LabReportCore;
import com.aizhixin.lab.common.dto.SortDTO;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.company.service.UserService;
import com.aizhixin.lab.course.classes.dto.LabClassDTO;
import com.aizhixin.lab.course.classes.dto.StuReportDTO;
import com.aizhixin.lab.course.classes.repository.CourseClassStuRepository;
import com.aizhixin.lab.course.classes.repository.LabClassRepository;
import com.aizhixin.lab.course.classes.repository.LabReportRepository;
import com.aizhixin.lab.course.course.domain.CourseDomain;
import com.aizhixin.lab.course.course.entity.CourseChapter;
import com.aizhixin.lab.course.course.repository.CourseChapterRepository;
import com.aizhixin.lab.course.course.repository.CourseRepository;

@Slf4j
@Service
@Transactional
public class LabReportService {
    @Autowired
    private LabReportRepository labReportRepository;
    @Autowired
    private LabClassRepository labClassRepository;
    @Autowired
    private CourseChapterRepository courseChapterRepository;
    @Autowired
    private CourseRepository courseRepository;
    @Autowired
    private UserService userService;
    @Autowired
    private CourseClassService courseClassService;
    @Autowired
    private CourseClassStuRepository courseClassStuRepository;
    @Autowired
    private PageJdbcUtil pageJdbcUtil;
    @Autowired
    private ReportFileRepository fileRepository;

    public void deleteByStuIds(List<Long> stuIds) {
        labReportRepository.updateByStuId(DataValidity.INVALID.getState(), stuIds);
    }


    public List<LabReport> initClassLabReport(List<CourseClass> classList) {

        List<LabReport> reportList = new ArrayList<LabReport>();
        List<LabClass> labList = new ArrayList<LabClass>();
        // 查询有关联实验环境的章节列表
        List<CourseChapter> chapterList = null;
        // 初始化行政班学生的实验报告
        if (!classList.isEmpty()) {
            for (CourseClass courseClass : classList) {

                if (null == chapterList) {
                    chapterList = courseChapterRepository
                            .findAllByCourseIdAndDeleteFlagAndVmNameIsNotNull(courseClass.getCourseId(), DataValidity.VALID.getState());
                }
                // 过滤掉选修班记录
                if (null != courseClass.getClassId() && courseClass.getClassId() > 0L) {
                    List<Long> userIds = userService.getUserIdByClassId(courseClass.getClassId());
                    if (null != userIds && !userIds.isEmpty()) {
                        for (Long userId : userIds) {
                            for (CourseChapter chapter : chapterList) {
                                LabReport report = new LabReport();
                                report.setId(UUID.randomUUID().toString());
                                report.setCourseId(courseClass.getCourseId());
                                report.setTeacherId(courseClass.getTeacherId());
                                report.setCourseClassId(courseClass.getId());
                                report.setChaperId(chapter.getId());
                                report.setClassId(courseClass.getClassId());
                                report.setStuId(userId);
                                report.setReviewResult(LabReportCore.REPORT_STATUS_INIT);
                                reportList.add(report);
                            }
                        }
                    }
                }

                for (CourseChapter chapter : chapterList) {
                    LabClass labClass = new LabClass();
                    labClass.setId(UUID.randomUUID().toString());
                    labClass.setCourseId(courseClass.getCourseId());
                    labClass.setTeacherId(courseClass.getTeacherId());
                    labClass.setCourseClassId(courseClass.getId());
                    labClass.setChaperId(chapter.getId());
                    labClass.setClassId(courseClass.getClassId());
                    labList.add(labClass);
                }
            }
        }
        labReportRepository.save(reportList);
        labClassRepository.save(labList);
    return reportList;
    }

    public void initStuLabReport(Long teacherId, String courseId, String courseClassId, ArrayList<Long> userIdList) {
        // 查询有关联实验环境的章节列表
        List<CourseChapter> chapterList = courseChapterRepository
                .findAllByCourseIdAndDeleteFlagAndVmNameIsNotNull(courseId, DataValidity.VALID.getState());
        if (null != chapterList && !chapterList.isEmpty()) {
            ArrayList<LabReport> reportList = new ArrayList<LabReport>();
            ArrayList<LabClass> labList = new ArrayList<LabClass>();
            // 初始化选修班学生的实验报告
            if (null != userIdList && !userIdList.isEmpty()) {
                for (Long userId : userIdList) {
                    for (CourseChapter chapter : chapterList) {
                        LabReport report = new LabReport();
                        report.setId(UUID.randomUUID().toString());
                        report.setCourseId(courseId);
                        report.setTeacherId(teacherId);
                        report.setCourseClassId(courseClassId);
                        report.setChaperId(chapter.getId());
                        report.setStuId(userId);
                        report.setReviewResult(LabReportCore.REPORT_STATUS_INIT);
                        reportList.add(report);
                    }
                }
            }

            for (CourseChapter chapter : chapterList) {
                LabClass labClass = new LabClass();
                labClass.setId(UUID.randomUUID().toString());
                labClass.setCourseId(courseId);
                labClass.setTeacherId(teacherId);
                labClass.setCourseClassId(courseClassId);
                labClass.setChaperId(chapter.getId());
                labClass.setStuNum(userIdList.size());
                labList.add(labClass);
            }

            labReportRepository.save(reportList);
            labClassRepository.save(labList);
        }
    }


    public void initChapterLabReport(Long teacherId, String courseId, Long chaperId) {

        Long chaperResultCount = labClassRepository.countByChaperId(chaperId);
        if (null != chaperResultCount && chaperResultCount > 0) {
            labClassRepository.deleteByChaperId(chaperId);
            labReportRepository.deleteByChaperId(chaperId);
        }
        //查询有课程班课列表
        List<CourseClass> classList = courseClassService.findCourseClassByCourseId(courseId);
        if (null != classList && !classList.isEmpty()) {
            ArrayList<LabReport> reportList = new ArrayList<LabReport>();
            ArrayList<LabClass> labList = new ArrayList<LabClass>();
            for (CourseClass courseClass : classList) {
                //行政班实验报告学生初始化
                if (null != courseClass.getClassId() && courseClass.getClassId() > 0L) {
                    List<Long> userIds = userService.getUserIdByClassId(courseClass.getClassId());
                    if (null != userIds && !userIds.isEmpty()) {
                        for (Long userId : userIds) {
                            LabReport report = new LabReport();
                            report.setId(UUID.randomUUID().toString());
                            report.setCourseId(courseClass.getCourseId());
                            report.setTeacherId(courseClass.getTeacherId());
                            report.setCourseClassId(courseClass.getId());
                            report.setChaperId(chaperId);
                            report.setClassId(courseClass.getClassId());
                            report.setStuId(userId);
                            report.setReviewResult(LabReportCore.REPORT_STATUS_INIT);
                            reportList.add(report);
                        }
                    }
                    LabClass labClass = new LabClass();
                    labClass.setId(UUID.randomUUID().toString());
                    labClass.setCourseId(courseId);
                    labClass.setTeacherId(teacherId);
                    labClass.setCourseClassId(courseClass.getId());
                    labClass.setChaperId(chaperId);
                    labClass.setStuNum(reportList.size());
                    labClass.setClassId(courseClass.getClassId());
                    labList.add(labClass);
                } else {//选修班实验报告学生初始化
                    List<CourseClassStu> stuList = courseClassStuRepository.findAllByDeleteFlagAndCourseClassId(DataValidity.VALID.getState(), courseClass.getId());
                    for (CourseClassStu stu : stuList) {
                        LabReport report = new LabReport();
                        report.setId(UUID.randomUUID().toString());
                        report.setCourseId(courseId);
                        report.setTeacherId(teacherId);
                        report.setCourseClassId(courseClass.getId());
                        report.setChaperId(chaperId);
                        report.setStuId(stu.getStuId());
                        report.setReviewResult(LabReportCore.REPORT_STATUS_INIT);
                        reportList.add(report);
                    }
                    LabClass labClass = new LabClass();
                    labClass.setId(UUID.randomUUID().toString());
                    labClass.setCourseId(courseId);
                    labClass.setTeacherId(teacherId);
                    labClass.setCourseClassId(courseClass.getId());
                    labClass.setChaperId(chaperId);
                    labClass.setStuNum(stuList.size());
                    labList.add(labClass);
                }
            }
            labReportRepository.save(reportList);
            labClassRepository.save(labList);
        }
    }

    public long deleteByCourseClassId(String courseClassId) {
        labClassRepository.deleteByCourseClassId(courseClassId);
        return labReportRepository.deleteByCourseClassId(courseClassId);
    }

    public long countCommitNumByCourseClassId(String courseClassId) {
        return labReportRepository.countByCourseClassIdAndCommitTimeIsNotNull(courseClassId);
    }


    public Map<String, Object> save(LabReportDomain dto) {
        Map<String, Object> result = new HashMap<>();
        LabReport labReport = labReportRepository.findOne(dto.getId());
        if (labReport != null) {
            labReport.setContent(dto.getContent());
            labReport.setCommitTime(new Date());
            labReport.setReviewResult(LabReportCore.REPORT_STATUS_UNCHECKED);
            fileRepository.deleteByReportId(dto.getId());
            List<ReportFile> reportFileList =new ArrayList<>();
            dto.getReportFileDomainList().forEach(item->{
                ReportFile reportFile = new ReportFile();
                BeanUtils.copyProperties(item,reportFile);
                reportFile.setReportId(dto.getId());
                reportFileList.add(reportFile);
            });
            fileRepository.save(reportFileList);
            labReportRepository.save(labReport);
            //将该章节下初始化的实验报告改为待审核状态
            Long countNum = labReportRepository.countByChaperIdAndReviewResultAndDeleteFlag(labReport.getChaperId(), LabReportCore.REPORT_STATUS_INIT, DataValidity.VALID.getState());
            if (null != countNum && countNum > 0) {
                labReportRepository.updateByChaperId(LabReportCore.REPORT_STATUS_UNCOMMIT, labReport.getChaperId(), DataValidity.VALID.getState(), dto.getId());
            }
            result.put(ApiReturnConstants.SUCCESS, true);
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "实验报告信息不存在！");
        }
        return result;
    }


    public Map<String, Object> getStuReport(Long stuId, Long chaperId) {
        Map<String, Object> result = new HashMap<>();
        List<LabReport> labReportList = labReportRepository.findAllByStuIdAndChaperIdAndDeleteFlag(stuId, chaperId, DataValidity.VALID.getState());
        if (null != labReportList && !labReportList.isEmpty()) {
            LabReport labReport = labReportList.get(0);
            LabReportInfoDomain labReportInfoDomain = new LabReportInfoDomain();
            BeanUtils.copyProperties(labReport,labReportInfoDomain);
            List<ReportFile> reportFileList = fileRepository.findByReportId(labReport.getId());
            List<ReportFileDomain> reportFileDomainList=new ArrayList<>();
            reportFileList.forEach(item->{
                ReportFileDomain reportFileDomain = new ReportFileDomain();
                BeanUtils.copyProperties(item,reportFileDomain);
                reportFileDomainList.add(reportFileDomain);
            });
            labReportInfoDomain.setReportFileDomainList(reportFileDomainList);
            result.put(ApiReturnConstants.DATA, labReportInfoDomain);
            result.put(ApiReturnConstants.SUCCESS, true);
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "实验报告信息不存在！");
        }
        return result;
    }
    RowMapper<LabReportInfoDomain> lrd = new RowMapper<LabReportInfoDomain>() {

        @Override
        public LabReportInfoDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            LabReportInfoDomain domain = new LabReportInfoDomain();
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setContent(rs.getString("content"));
            domain.setStuName(rs.getString("stu_name"));
            domain.setCommitTime(rs.getDate("commit_time"));
            return domain;
        }
    };
    public LabReportInfoDomain getLabReport(String id){
        String sql="SELECT tu.`name` stu_name,cc.`name` course_name,ccc.`name` chapter_name,tlr.COMMIT_TIME commit_time,tlr.CONTENT content FROM t_lab_report tlr LEFT JOIN c_course cc ON cc.id=tlr.COURSE_ID LEFT JOIN c_course_chapter ccc on ccc.id=tlr.CHAPER_ID LEFT JOIN t_user tu ON tu.id=tlr.STU_ID WHERE tlr.ID='"+id+"'";
        List<LabReportInfoDomain> info = pageJdbcUtil.getInfo(sql, lrd);
        if (info.size()>0){
            LabReportInfoDomain labReportInfoDomain = info.get(0);
            List<ReportFile> reportFileList = fileRepository.findByReportId(id);
            List<ReportFileDomain> reportFileDomainList=new ArrayList<>();
            reportFileList.forEach(item->{
                ReportFileDomain reportFileDomain = new ReportFileDomain();
                BeanUtils.copyProperties(item,reportFileDomain);
                reportFileDomainList.add(reportFileDomain);
            });
            labReportInfoDomain.setReportFileDomainList(reportFileDomainList);

            return labReportInfoDomain;
        }
        return null;
    }


    public Map<String, Object> teacherStatistics(Long teacherId) {
        Map<String, Object> result = new HashMap<>();
        Long reportNum = labReportRepository.countByTeacherIdAndReviewResultAndDeleteFlag(teacherId, LabReportCore.REPORT_STATUS_UNCHECKED, DataValidity.VALID.getState());
        Long courseNum = courseRepository.countByCreatedByAndIsPublishAndDeleteFlag(teacherId, false, DataValidity.VALID.getState());
        HashMap<String, Long> countMap = new HashMap<String, Long>();
        countMap.put("courseNum", courseNum);
        countMap.put("reportNum", reportNum);
        result.put(ApiReturnConstants.DATA, countMap);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }

    public Map<String, Object> stuStatistics(Long stuId) {
        Map<String, Object> result = new HashMap<>();
        ArrayList<String> reviewResults = new ArrayList<String>();
        reviewResults.add(LabReportCore.REPORT_STATUS_UNCHECKED);
        reviewResults.add(LabReportCore.REPORT_STATUS_PASS);
        reviewResults.add(LabReportCore.REPORT_STATUS_NOT_PASS);
        Long reportNum = labReportRepository.countByStuIdAndReviewResultInAndDeleteFlag(stuId, reviewResults, DataValidity.VALID.getState());
        Long courseNum = getStuJoinCourseNum(stuId);
        HashMap<String, Long> countMap = new HashMap<String, Long>();
        countMap.put("courseNum", courseNum);
        countMap.put("reportNum", reportNum);
        result.put(ApiReturnConstants.DATA, countMap);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }

    public Long getStuJoinCourseNum(Long stuId) {
        String count_sql = "select COUNT(*) from `t_course_class_stu` ccs  LEFT JOIN `t_course_class` cc ON cc.id = ccs.course_class_id LEFT JOIN `c_course` c ON c.id =cc.course_id where ccs.stu_id =" + stuId + " and c.delete_flag =0 and ccs.delete_flag =0 and c.is_archive = false and c.is_publish = true";
        Long count = pageJdbcUtil.getCount(count_sql);

        return count;
    }

    RowMapper<CourseDomain> rm1 = new RowMapper<CourseDomain>() {

        @Override
        public CourseDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            CourseDomain domain = new CourseDomain();
            domain.setId(rs.getString("id"));
            domain.setName(rs.getString("name"));
            return domain;
        }
    };

    public Map<String, Object> getStuCourseList(Long stuId) {
        Map<String, Object> result = new HashMap<>();
        String sql = "select c.`id`,c.`name` from `t_course_class_stu` ccs LEFT JOIN `t_course_class` cc ON cc.id = ccs.course_class_id LEFT JOIN `c_course` c ON c.id =cc.course_id where ccs.stu_id =" + stuId + " and ccs.delete_flag =0 and c.delete_flag =0 and c.is_archive = false and c.is_publish = true";
        List<CourseDomain> list = pageJdbcUtil.getInfo(sql, rm1);
        result.put(ApiReturnConstants.DATA, list);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }


    public Map<String, Object> review(LabReportDomain dto) {
        Map<String, Object> result = new HashMap<>();
        LabReport labReport = labReportRepository.findOne(dto.getId());
        if (labReport != null) {
            labReport.setReviewResult(dto.getReviewResult());
            labReport.setTeacherContent(dto.getContent());
            labReportRepository.save(labReport);
            result.put(ApiReturnConstants.SUCCESS, true);
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "实验报告信息不存在！");
        }
        return result;
    }

    RowMapper<LabClassDTO> rm = new RowMapper<LabClassDTO>() {

        @Override
        public LabClassDTO mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            LabClassDTO domain = new LabClassDTO();
            domain.setCourseClassId(rs.getString("COURSE_CLASS_ID"));
            domain.setChapterId(rs.getLong("CHAPER_ID"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setClassName(rs.getString("class_name"));
            domain.setChapterName(rs.getString("chapter_name"));
            if (StringUtils.isEmpty(domain.getClassName())) {
                domain.setStuNum(rs.getInt("STU_NUM"));
            } else {
                domain.setStuNum(rs.getInt("class_stu_num"));
            }
            domain.setCommitNum(rs.getInt("commit_num"));
            domain.setCourseId(rs.getString("COURSE_ID"));
            return domain;
        }
    };

    public <T> Map<String, Object> findlabClassPage(QueryLabReportDomain domain) {

        String querySql = "select lc.COURSE_ID,lc.COURSE_CLASS_ID,lc.CHAPER_ID,c.`name` as course_name,cl.name as class_name,cc.`name` as chapter_name,(select count(*) from `t_user` u where u.delete_flag =0 and u.CLASS_ID =lc.CLASS_ID) as class_stu_num,lc.STU_NUM,(select count(*) from `t_lab_report` lr where lr.DELETE_FLAG =0 and COMMIT_TIME is not null and lr.COURSE_CLASS_ID=lc.COURSE_CLASS_ID and lr.CHAPER_ID =lc.CHAPER_ID) as commit_num from `t_lab_class` lc LEFT JOIN `c_course` c ON lc.COURSE_ID = c.id LEFT JOIN `t_classes` cl ON lc.CLASS_ID = cl.ID LEFT JOIN `c_course_chapter` cc ON cc.id = lc.CHAPER_ID where lc.DELETE_FLAG =0 ";
        String countSql = "select count(*) from `t_lab_class` lc where lc.DELETE_FLAG =0 ";
        if (!StringUtils.isEmpty(domain.getCourseId())) {
            querySql += " and lc.COURSE_ID='" + domain.getCourseId() + "'";
            countSql += " and lc.COURSE_ID='" + domain.getCourseId() + "'";
        }
        if (null != domain.getChapterId() && domain.getChapterId().intValue() > 0L) {
            querySql += " and lc.CHAPER_ID=" + domain.getChapterId() + "";
            countSql += " and lc.CHAPER_ID=" + domain.getChapterId() + "";
        }
        if (!StringUtils.isEmpty(domain.getCourseClassId())) {
            querySql += " and lc.COURSE_CLASS_ID='" + domain.getCourseClassId() + "'";
            countSql += " and lc.COURSE_CLASS_ID='" + domain.getCourseClassId() + "'";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("cl.CREATED_DATE");
        dto.setAsc(false);
        sort.add(dto);

        return pageJdbcUtil.getPageInfor(domain.getPageSize(),
                domain.getPageNumber(), rm, sort, querySql, countSql);
    }


    RowMapper<StuReportDTO> stuRm = new RowMapper<StuReportDTO>() {

        @Override
        public StuReportDTO mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            StuReportDTO domain = new StuReportDTO();
            domain.setId(rs.getString("report_id"));
            domain.setContent(rs.getString("CONTENT"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setClassName(rs.getString("class_name"));
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setReviewResult(rs.getString("REVIEW_RESULT"));
            domain.setCommitTime(rs.getTimestamp("COMMIT_TIME"));
            domain.setStuName(rs.getString("stu_name"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setStuId(rs.getLong("STU_ID"));
            domain.setTeacherContent(rs.getString("teacher_content"));
            return domain;
        }
    };

    public <T> Map<String, Object> findlabStuPage(QueryLabReportDomain domain) {

        String querySql = "SELECT lr.STU_ID,lr.id as report_id,lr.CONTENT,lr.REVIEW_RESULT,lr.teacher_content teacher_content,lr.COMMIT_TIME,u.`name` as stu_name,cl.`NAME` as class_name,cc.`name` as chapter_name,c.`name` as course_name FROM `t_lab_report` lr LEFT JOIN `c_course` c ON lr.COURSE_ID = c.id LEFT JOIN `t_user` u ON lr.stu_id = u.id LEFT JOIN `t_classes` cl ON cl.id = lr.class_id LEFT JOIN `c_course_chapter` cc ON lr.CHAPER_ID = cc.id where lr.DELETE_FLAG =0 ";
        String countSql = "SELECT count(*) FROM `t_lab_report` lr LEFT JOIN `t_user` u ON lr.stu_id = u.id where lr.DELETE_FLAG =0 ";

        if (null != domain.getTeacherId() && domain.getTeacherId() > 0) {
            querySql += " and lr.TEACHER_ID=" + domain.getTeacherId() + "";
            countSql += " and lr.TEACHER_ID=" + domain.getTeacherId() + "";
        }

        if (null != domain.getStuId() && domain.getStuId() > 0) {
            querySql += " and lr.STU_ID=" + domain.getStuId() + "";
            countSql += " and lr.STU_ID=" + domain.getStuId() + "";
        }
        if (!StringUtils.isEmpty(domain.getCourseId())) {
            querySql += " and lr.COURSE_ID='" + domain.getCourseId() + "'";
            countSql += " and lr.COURSE_ID='" + domain.getCourseId() + "'";
        }
        if (null != domain.getChapterId() && domain.getChapterId().intValue() > 0L) {
            querySql += " and lr.CHAPER_ID=" + domain.getChapterId() + "";
            countSql += " and lr.CHAPER_ID=" + domain.getChapterId() + "";
        }
        if (!StringUtils.isEmpty(domain.getCourseClassId())) {
            querySql += " and lr.COURSE_CLASS_ID='" + domain.getCourseClassId() + "'";
            countSql += " and lr.COURSE_CLASS_ID='" + domain.getCourseClassId() + "'";
        }
        if (!StringUtils.isEmpty(domain.getName())) {
            querySql += " and u.NAME like '%" + domain.getName() + "%'";
            countSql += " and u.NAME like '%" + domain.getName() + "%'";
        }
        if (!StringUtils.isEmpty(domain.getStatus())) {
            if(domain.getStatus().equals("approved")){
                querySql += " and lr.REVIEW_RESULT  not in ('"+LabReportCore.REPORT_STATUS_INIT+"','"+LabReportCore.REPORT_STATUS_UNCOMMIT+"','"+LabReportCore.REPORT_STATUS_UNCHECKED+"')";
                countSql += " and lr.REVIEW_RESULT  not in ('"+LabReportCore.REPORT_STATUS_INIT+"','"+LabReportCore.REPORT_STATUS_UNCOMMIT+"','"+LabReportCore.REPORT_STATUS_UNCHECKED+"')";
            }else {
                querySql += " and lr.REVIEW_RESULT ='" + domain.getStatus() + "'";
                countSql += " and lr.REVIEW_RESULT ='" + domain.getStatus() + "'";
            }
        } else {
            if (null != domain.getStuId() && domain.getStuId() > 0) {
                querySql += " and lr.REVIEW_RESULT !='" + LabReportCore.REPORT_STATUS_INIT + "'";
                countSql += " and lr.REVIEW_RESULT !='" + LabReportCore.REPORT_STATUS_INIT + "'";
            } else {
                querySql += " and lr.REVIEW_RESULT !='" + LabReportCore.REPORT_STATUS_INIT + "'";
                countSql += " and lr.REVIEW_RESULT !='" + LabReportCore.REPORT_STATUS_INIT + "'";
            }
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("lr.CREATED_DATE");
        dto.setAsc(false);
        sort.add(dto);

        return pageJdbcUtil.getPageInfor(domain.getPageSize(),
                domain.getPageNumber(), stuRm, sort, querySql, countSql);
    }

    RowMapper<ClassReportDomain> crd = new RowMapper<ClassReportDomain>() {

        @Override
        public ClassReportDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ClassReportDomain domain = new ClassReportDomain();
            domain.setCourseClassId(rs.getString("COURSE_CLASS_ID"));
            domain.setChapterId(rs.getLong("chapter_id"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setClassName(rs.getString("class_name") == null ? "选修班" : rs.getString("class_name"));
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setCourseName(rs.getString("course_name"));
            if (StringUtils.isEmpty(rs.getString("class_name"))) {
                domain.setTotalNum(rs.getInt("total_num"));
            } else {
                domain.setTotalNum(rs.getInt("class_stu_num"));
            }
            domain.setCommitNum(getCommitReportNum(domain.getCourseClassId(), domain.getChapterId()));
            return domain;
        }
    };

    /**
     * 获取班级报告列表
     *
     * @param domain
     * @param teacherId
     * @param <T>
     * @return
     */
    public <T> Map<String, Object> getClassReportList(QueryLabReportDomain domain, Long teacherId) {
        String querySql = "SELECT cc.`name` course_name,(select count(*) from `t_user` u where u.delete_flag =0 and u.CLASS_ID =tlc.CLASS_ID) as class_stu_num,tlc.COURSE_CLASS_ID COURSE_CLASS_ID, tc.`NAME` class_name,ccc.id chapter_id, ccc.`name` chapter_name,tlc.STU_NUM total_num FROM t_lab_class tlc LEFT JOIN c_course cc ON tlc.COURSE_ID=cc.id LEFT JOIN c_course_chapter ccc ON tlc.CHAPER_ID=ccc.id LEFT JOIN t_classes tc ON tlc.CLASS_ID=tc.ID WHERE tlc.DELETE_FLAG=0 AND tlc.TEACHER_ID=" + teacherId;
        String countSql = "SELECT count(*) FROM t_lab_class tlc LEFT JOIN c_course cc ON tlc.COURSE_ID=cc.id LEFT JOIN c_course_chapter ccc ON tlc.CHAPER_ID=ccc.id LEFT JOIN t_classes tc ON tlc.CLASS_ID=tc.ID WHERE tlc.DELETE_FLAG=0 AND tlc.TEACHER_ID=" + teacherId;
        if (!StringUtils.isEmpty(domain.getCourseId())) {
            querySql += " AND tlc.COURSE_ID='" + domain.getCourseId() + "'";
            countSql += " AND tlc.COURSE_ID='" + domain.getCourseId() + "'";
        }
        if (domain.getChapterId() != null && domain.getChapterId() > 0L) {
            querySql += " AND tlc.CHAPER_ID=" + domain.getChapterId();
            countSql += " AND tlc.CHAPER_ID=" + domain.getChapterId();
        }
        if (!StringUtils.isEmpty(domain.getCourseClassId())) {
            querySql += " AND tlc.COURSE_CLASS_ID='" + domain.getCourseClassId() + "'";
            querySql += " AND tlc.COURSE_CLASS_ID='" + domain.getCourseClassId() + "'";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("tlc.CREATED_DATE");
        dto.setAsc(false);
        sort.add(dto);
        return pageJdbcUtil.getPageInfor(domain.getPageSize(), domain.getPageSize(), crd, sort, querySql, countSql);
    }

    public Integer getCommitReportNum(String classId, Long chapterId) {
        String sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.CHAPER_ID=" + chapterId + " AND t.COURSE_CLASS_ID='" + classId + "' AND t.REVIEW_RESULT  not in('init','uncommit')";
        return pageJdbcUtil.getCountInt(sql);
    }

    RowMapper<ReportDomain> rd = new RowMapper<ReportDomain>() {

        @Override
        public ReportDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ReportDomain domain = new ReportDomain();
            domain.setId(rs.getString("id"));
            domain.setClassName(rs.getString("class_name") == null ? "选修班" : rs.getString("class_name"));
            domain.setStuName(rs.getString("user_name"));
            String result = rs.getString("result");
            if (LabReportCore.REPORT_STATUS_INIT.equals(result)){
            	domain.setStatus("未开始");
            	domain.setScore("未打分");
			}else if(LabReportCore.REPORT_STATUS_UNCOMMIT.equals(result)){
            	domain.setStatus("未提交");
				domain.setScore("未打分");
			}else if (LabReportCore.REPORT_STATUS_UNCHECKED.equals(result)){
            	domain.setStatus("待批阅");
            	domain.setScore("未打分");
			}else {
				domain.setStatus("已批阅");
				domain.setScore(result);
			}
            return domain;
        }
    };

    public List<ReportDomain> getChapterReport(Long chapter,String courseClassId,String status) {

		String sql="SELECT t.id id, tc.`NAME` class_name,tu.`name` user_name,t.COMMIT_TIME commit_time,t.REVIEW_RESULT result FROM t_lab_report t LEFT JOIN t_classes tc ON t.CLASS_ID=tc.id LEFT JOIN t_user tu ON tu.id=t.STU_ID  WHERE t.DELETE_FLAG=0 AND t.CHAPER_ID="+chapter+" AND t.COURSE_CLASS_ID='"+courseClassId+"'";
        if (!StringUtils.isEmpty(status)){
            if(status.equals("approved")){
                sql += " and t.REVIEW_RESULT  not in ('"+LabReportCore.REPORT_STATUS_INIT+"','"+LabReportCore.REPORT_STATUS_UNCOMMIT+"','"+LabReportCore.REPORT_STATUS_UNCHECKED+"')";
            }else {
                sql += " and t.REVIEW_RESULT ='" + status + "'";
            }
        }
		return pageJdbcUtil.getInfo(sql,rd);
    }
    public ClassReportDomain getReport(Long chapter,String courseClassId){
        String querySql = "SELECT cc.`name` course_name,(select count(*) from `t_user` u where u.delete_flag =0 and u.CLASS_ID =tlc.CLASS_ID) as class_stu_num,tlc.COURSE_CLASS_ID COURSE_CLASS_ID, tc.`NAME` class_name,ccc.id chapter_id, ccc.`name` chapter_name,tlc.STU_NUM total_num FROM t_lab_class tlc LEFT JOIN c_course cc ON tlc.COURSE_ID=cc.id LEFT JOIN c_course_chapter ccc ON tlc.CHAPER_ID=ccc.id LEFT JOIN t_classes tc ON tlc.CLASS_ID=tc.ID WHERE tlc.DELETE_FLAG=0";
        querySql += " AND tlc.CHAPER_ID=" + chapter;
        querySql += " AND tlc.COURSE_CLASS_ID='" + courseClassId + "'";
        List<ClassReportDomain> info = pageJdbcUtil.getInfo(querySql, crd);
        return info.get(0);

    }

}
